package com.vecspace.msoffice;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.vecspace.exception.HException;
import com.vecspace.utils.Convert;
import com.vecspace.utils.FileHelper;

public class CellTableWriter {

	/**将多个CellTable写入同一个excel的多个sheet
	 * 
	 * @param templateFilePath
	 * @param cellTables
	 * @param startRowIndex 从第几行开始输出cellTable中的内容（startRowIndex之前的内容可能为模块内容），0-based 第一行==0
	 * @param startColumnIndex 从第几列开始输出cellTable中的内容，0-based 第一列A==0 
	 * @param outputStream 将模板文件输出到outputStream中,outputStream作为参数传入，能保证调用者对其进行释放。
	 * @author: wanghua
	 * @throws Exception 
	 */
	public void writeXSSFStream(String templateFilePath, List<CellTable> cellTables, int startRowIndex, int startColumnIndex, ByteArrayOutputStream outputStream) throws Exception {
		if(templateFilePath == null || !templateFilePath.toLowerCase().endsWith(".xlsx")){
			throw new HException("模板文件必须是Excel2007以上格式（.xlsx）");
		}
//		File file = new File(templateFilePath);
//		if(!file.exists()) {
//			throw new HException("模板文件不存在：" + file.getAbsolutePath());
//		}		
		
		XSSFWorkbook wbook = null;
		SXSSFWorkbook wbook2 = null;
		try {
			InputStream fileInStream = FileHelper.openFile(templateFilePath);
			if(fileInStream == null) {
				throw new HException("模板文件不存在：" + templateFilePath);
			}
			try{
				wbook = new XSSFWorkbook(fileInStream);
			}finally{
				fileInStream.close();
			}			 
			
			//将第一个sheet复制多份，保证模板被复制。
			for(int i=1; i< cellTables.size(); ++i) {
				wbook.cloneSheet(0);
			}
			//给每个sheet重命名
			for(int i=0; i< cellTables.size(); ++i) {
				CellTable cellTable = cellTables.get(i);
				int sheetIndex = i;
				String sheetName = cellTable.getTableName();
				if(sheetName != null && sheetName.length() > 0) {
					wbook.setSheetName(sheetIndex, sheetName);
				}
				
			}
			//SXSSFWorkbook只在内存中留1000行，不会占用过多的内存
			wbook2 = new SXSSFWorkbook(wbook,1000);
			for(int i=0; i< cellTables.size(); ++i) {
				CellTable cellTable = cellTables.get(i);
				int sheetIndex = i;
				SXSSFSheet sheet = wbook2.getSheetAt(sheetIndex);			
				this.writeCellTable(sheet, cellTable, startRowIndex, startColumnIndex);
			}
			wbook2.write(outputStream);
			outputStream.flush();
		}finally{
			if(wbook != null) wbook.close();
			if(wbook2 != null) wbook2.close();
		}	
	}
	
	/**
	 * 
	 * @param filePath 要写入的excel文件的完成路径。excel必须已经存在
	 * @param cellTable
	 * @param sheetIndex 0-based
	 * @param sheetName
	 * @param startRowIndex 从第几行开始输出cellTable中的内容（startRowIndex之前的内容可能为模块内容），0-based 第一行==0
	 * @param startColumnIndex 从第几列开始输出cellTable中的内容，0-based 第一列A==0 
	 * @author: wanghua
	 * @throws Exception 
	 */
	public void writeXSSF(String filePath, CellTable cellTable, int sheetIndex, String sheetName, int startRowIndex, int startColumnIndex) throws Exception {
		if(filePath == null || !filePath.toLowerCase().endsWith(".xlsx")){
			throw new HException("文件必须是Excel2007以上格式（.xlsx）");
		}
		File file = new File(filePath);
		if(!file.exists()) {
			throw new HException("文件不存在：" + file.getAbsolutePath());
		}
		
		SXSSFWorkbook wbook = null;
		try {
			try(FileInputStream fileInStream = new FileInputStream(filePath)){				
				wbook  = new SXSSFWorkbook(new XSSFWorkbook(fileInStream),1000);
			}
			while(sheetIndex >= wbook.getNumberOfSheets()) {
				wbook.createSheet();
			}
			if(sheetName != null && sheetName.length() > 0) {
				wbook.setSheetName(sheetIndex, sheetName);
			}
			SXSSFSheet sheet = wbook.getSheetAt(sheetIndex);			
			this.writeCellTable(sheet, cellTable, startRowIndex, startColumnIndex);
			try(FileOutputStream fileOutStream = new FileOutputStream(filePath)){				
				wbook.write(fileOutStream);
				fileOutStream.flush();
			}
		}finally{
			if(wbook != null) wbook.close();
		}		
	}
	
	/**将cellTable的数据循环写入sheet中
	 * 
	 * @param sheet
	 * @param cellTable
	 * @param startRowIndex
	 * @param startColumnIndex
	 * @param mergeRowCells
	 * @throws Exception 
	 * @author: wanghua
	 */
	/**
	 * @param sheet
	 * @param cellTable
	 * @param startRowIndex
	 * @param startColumnIndex
	 * @throws Exception 
	 */
	public void writeCellTable(SXSSFSheet sheet, CellTable cellTable, int startRowIndex, int startColumnIndex) throws Exception {
		
		int tableColumnCount = cellTable.getColumnCount();
		int tableRowCount = cellTable.getRowCount();
		
		//设置列宽
		for(int tableColumnIndex = 0; tableColumnIndex < tableColumnCount; ++tableColumnIndex) {
			CellColumn cellColumn = cellTable.getColumn(tableColumnIndex);
			int colIndex = tableColumnIndex + startColumnIndex;
			if(cellColumn.getWidth() > 0) {
				sheet.setColumnWidth(colIndex, cellColumn.getWidth());
			}		
		}
		
		SXSSFWorkbook workbook = sheet.getWorkbook();
		//以下将CellTable的数据写入excel sheet的单元格
		for(int tableRowIndex = 0; tableRowIndex < tableRowCount; ++tableRowIndex) {
			CellRow cellRow = cellTable.getRow(tableRowIndex);
			int rowIndex = tableRowIndex + startRowIndex;
			SXSSFRow row = sheet.createRow(rowIndex);
			CellStyle cellStyle = workbook.createCellStyle();
			Font fontStyle = workbook.createFont();
			fontStyle.setFontName("微软雅黑"); // 字体
			fontStyle.setFontHeightInPoints((short) 10); // 大小
			cellStyle.setBorderBottom(BorderStyle.THIN);//设置边框
			cellStyle.setBorderLeft(BorderStyle.THIN);//设置边框
			cellStyle.setBorderRight(BorderStyle.THIN);//设置边框
			cellStyle.setBorderTop(BorderStyle.THIN);//设置边框
			for(int tableColumnIndex = 0; tableColumnIndex < tableColumnCount; ++tableColumnIndex) {
				int colIndex = tableColumnIndex + startColumnIndex;
				Object cellObj = null;
				CellValue cellValue = cellRow.getValue(tableColumnIndex);
				SXSSFCell cell = row.createCell(colIndex);

				if(cellValue != null) {
					if(cellValue.isMergedCell() && !cellValue.isMergeReginFirsetCell()) {
						continue;
					}
					cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中  
					cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 
					cellObj = cellValue.getValue();				 
				 
					if(cellObj != null) {
						if(cellObj instanceof String) {					
							cell.setCellValue(cellObj.toString());
						}else if(cellObj instanceof Date) {
							cell.setCellValue((Date)cellObj);
						}else{
							double num = Convert.toDouble(cellObj, 0.0);
							cell.setCellValue(num);
						}
					}else {
						if(cellValue.getDataType() == CellDataType.string) {
							cell.setCellType(CellType.STRING);
						}else if(cellValue.getDataType() == CellDataType.numeric) {
							cell.setCellType(CellType.NUMERIC);
						}else if(cellValue.getDataType() == CellDataType.date) {
							cell.setCellType(CellType.NUMERIC);
						}
					}
				}
				cellStyle.setFont(fontStyle);
				cell.setCellStyle(cellStyle);
			}
		}
		

		//以下合并单元格
		cellTable.mergeCellsOfCoumns();
		for(int tableRowIndex = 0; tableRowIndex < tableRowCount; ++tableRowIndex) {
			CellRow cellRow = cellTable.getRow(tableRowIndex);
			int rowIndex = tableRowIndex + startRowIndex;
//			XSSFRow row = sheet.createRow(rowIndex);			
			for(int tableColumnIndex = 0; tableColumnIndex < tableColumnCount; ++tableColumnIndex) {
				int colIndex = tableColumnIndex + startColumnIndex;
				CellValue cellValue = cellRow.getValue(tableColumnIndex);
				if(cellValue != null) {
					if(cellValue.isMergedCell() && cellValue.isMergeReginFirsetCell()) {
						int rowSpan = cellValue.getRowSpan();
						int colSpan = cellValue.getColSpan();
						if(rowSpan < 1) rowSpan = 1;
						if(colSpan < 1) colSpan = 1;
						if(rowSpan > 1 || colSpan > 1) {
							CellRangeAddress mergedRegion = new CellRangeAddress(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex + colSpan - 1);
							sheet.addMergedRegion(mergedRegion);
						}
					}
				}
			}
		}
	}
}
